﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Data.Common;
using DBDataAccessLib.DataMapping;
using DaiKin.DBDataAccessLib.Helper;
using System.Data.SqlClient;

namespace DBDataAccessLib
{
    public abstract class UserDataAccess
    {
        public static AccountMapping SelectUserNameAndPasswordCount(string userName, string password)
        {
            AccountMapping account = null;
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectUserInfo,
                    new OracleParameter(":userName", userName),
                    new OracleParameter(":password", password)
                    );
            else
                reader = SqlHelper.ExecuteReader(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.SelectUserInfo,
                new SqlParameter("@userName", userName),
                new SqlParameter("@password", password)
                );
            while (reader.Read())
            {
                account = new AccountMapping();
                account.UserName = reader.GetString(0);
                account.Password = reader.GetString(1);
                account.Function = reader.GetString(2);
                break;
            }
            return account;
        }

        public static List<AccountMapping> GetAllAccounts()
        {
            List<AccountMapping> accounts = new List<AccountMapping>();
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectAllUsers
                    );
            else
                reader = SqlHelper.ExecuteReader(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectAllUsers
                    );
            while (reader.Read())
            {
                AccountMapping account = new AccountMapping();
                account.UserName = reader.GetString(0);
                account.Password = reader.GetString(1);
                account.Function = reader.GetString(2);
                accounts.Add(account);
            }
            return accounts;
        }

        public static int UpdateUser(string userName, string password, string function)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.UpdateUser,
                    new OracleParameter(":userName", userName),
                    new OracleParameter(":password", password),
                    new OracleParameter(":function", function)
                    );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.UpdateUser,
                    new SqlParameter("@userName", userName),
                    new SqlParameter("@password", password),
                    new SqlParameter("@function", function)
                    );
            return affectRows;
        }

        public static int AddUser(string userName, string password, string function)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.AddUser,
                new OracleParameter(":userName", userName),
                new OracleParameter(":password", password),
                new OracleParameter(":function", function)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.AddUser,
                    new SqlParameter("@userName", userName),
                    new SqlParameter("@password", password),
                    new SqlParameter("@function", function)
                    );
            return affectRows;
        }

        public static int DeleteUser(string userName)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteUser,
                new OracleParameter(":userName", userName)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteUser,
                new SqlParameter("@userName", userName)
                );
            return affectRows;
        }
    }
}
